﻿#if NETFRAMEWORK
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;
using NewLife;
using NewLife.Collections;
using NewLife.Log;
using NewLife.Reflection;

namespace XCode.DataAccessLayer;

/// <summary>SqlCe数据库。由 @Goon(12600112) 测试并完善正向反向工程</summary>
class SqlCe : FileDbBase
{
    #region 属性
    /// <summary>返回数据库类型。外部DAL数据库类请使用Other</summary>
    public override DatabaseType Type => DatabaseType.SqlCe;

    /// <summary>创建工厂</summary>
    /// <returns></returns>
    protected override DbProviderFactory CreateFactory()
    {
        // 找不到驱动时，再到线上下载
        var factory = GetProviderFactory(null, "System.Data.SqlServerCe.dll", "System.Data.SqlServerCe.SqlCeProviderFactory");
        if (factory != null)
        {
            using var conn = factory.CreateConnection();
            if (conn.ServerVersion.StartsWith("4"))
                SqlCeProviderVersion = SQLCEVersion.SQLCE40;
            else
                SqlCeProviderVersion = SQLCEVersion.SQLCE35;
        }

        return factory;
    }

    /// <summary>SqlCe提供者版本</summary>
    public static SQLCEVersion SqlCeProviderVersion { get; set; } = SQLCEVersion.SQLCE40;

    /// <summary>SqlCe版本,默认4.0</summary>
    public SQLCEVersion SqlCeVer { get; set; } = SQLCEVersion.SQLCE40;

    protected override void OnSetConnectionString(ConnectionStringBuilder builder)
    {
        base.OnSetConnectionString(builder);

        SqlCeVer = SQLCEVersion.SQLCE40;

        var fn = DatabaseName;
        if (!fn.IsNullOrEmpty() && File.Exists(fn))
        {
            try
            {
                SqlCeVer = SqlCeHelper.DetermineVersion(fn);
            }
            catch (Exception ex)
            {
                XTrace.WriteException(ex);

                SqlCeVer = SQLCEVersion.SQLCE40;
            }
        }
    }
    #endregion

    #region 方法
    /// <summary>创建数据库会话</summary>
    /// <returns></returns>
    protected override IDbSession OnCreateSession() => new SqlCeSession(this);

    /// <summary>创建元数据对象</summary>
    /// <returns></returns>
    protected override IMetaData OnCreateMetaData() => new SqlCeMetaData();
    #endregion

    #region 数据库特性
    protected override String ReservedWordsStr => "ADD,ALL,ALTER,AND,ANY,APPLY,AS,ASC,AUTHORIZATION,BACKUP,BEGIN,BETWEEN,BREAK,BROWSE,BULK,BY,CASCADE,CASE,CAST,CHECK,CHECKPOINT,CLOSE,CLUSTERED,COALESCE,COLLATE,COLUMN,COMMIT,COMPUTE,CONSTRAINT,CONTAINS,CONTAINSTABLE,CONTINUE,CONVERT,CREATE,CROSS,CURRENT,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,CURSOR,DATABASE,DBCC,DEALLOCATE,DECLARE,DEFAULT,DELETE,DENY,DESC,DISK,DISTINCT,DISTRIBUTED,DOUBLE,DROP,DUMP,ELSE,END,ERRLVL,ESCAPE,EXCEPT,EXEC,EXECUTE,EXISTS,EXIT,EXTERNAL,FETCH,FILE,FILLFACTOR,FIRST,FOR,FOREIGN,FREETEXT,FREETEXTTABLE,FROM,FULL,FUNCTION,GOTO,GRANT,GROUP,HAVING,HOLDLOCK,IDENTITY,IDENTITY_INSERT,IDENTITYCOL,IF,IN,INDEX,INNER,INSERT,INTERSECT,INTO,IS,JOIN,KEY,KILL,LEFT,LIKE,LINENO,LOAD,NATIONAL,NEXT,NOCHECK,NONCLUSTERED,NOT,NULL,NULLIF,OF,OFF,OFFSET,OFFSETS,ON,ONLY,OPEN,OPENDATASOURCE,OPENQUERY,OPENROWSET,OPENXML,OPTION,OR,ORDER,OUTER,OVER,PERCENT,PIVOT,PLAN,PRIMARY,PRINT,PROC,PROCEDURE,PUBLIC,RAISERROR,READ,READTEXT,RECONFIGURE,REFERENCES,REPLICATION,RESTORE,RESTRICT,RETURN,REVERT,REVOKE,RIGHT,ROLLBACK,ROW,ROWCOUNT,ROWGUIDCOL,ROWS,RULE,SAVE,SCHEMA,SELECT,SESSION_USER,SET,SETUSER,SHUTDOWN,SOME,STATISTICS,SYSTEM_USER,TABLE,TEXTSIZE,THEN,TO,TOP,TRAN,TRANSACTION,TRIGGER,TRUNCATE,TSEQUAL,UNION,UNIQUE,UNPIVOT,UPDATE,UPDATETEXT,USE,USER,VALUES,VARYING,VIEW,WAITFOR,WHEN,WHERE,WHILE,WITH,WRITETEXT,XMLUNNEST";

    ///// <summary>当前时间函数</summary>
    //public override String DateTimeNow { get { return "getdate()"; } }

    ///// <summary>最小时间</summary>
    //public override DateTime DateTimeMin { get { return SqlDateTime.MinValue.Value; } }

    ///// <summary>格式化时间为SQL字符串</summary>
    ///// <param name="dateTime">时间值</param>
    ///// <returns></returns>
    //public override String FormatDateTime(DateTime dateTime)
    //{
    //    return String.Format("'{0:yyyy-MM-dd HH:mm:ss}'", dateTime);
    //}

    /// <summary>格式化关键字</summary>
    /// <param name="keyWord">关键字</param>
    /// <returns></returns>
    public override String FormatKeyWord(String keyWord)
    {
        //if (String.IsNullOrEmpty(keyWord)) throw new ArgumentNullException("keyWord");
        if (String.IsNullOrEmpty(keyWord)) return keyWord;

        if (keyWord.StartsWith("[") && keyWord.EndsWith("]")) return keyWord;

        return $"[{keyWord}]";
    }
    #endregion

    #region 分页
    public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
    {
        var sql = PageSplit(builder.ToString(), startRowIndex, maximumRows, builder.Key);
        return new SelectBuilder(sql);
    }

    /// <summary>构造分页SQL</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="startRowIndex">开始行，0表示第一行</param>
    /// <param name="maximumRows">最大返回行数，0表示所有行</param>
    /// <param name="keyColumn">唯一键。用于not in分页</param>
    /// <returns>分页SQL</returns>
    public override String PageSplit(String sql, Int64 startRowIndex, Int64 maximumRows, String keyColumn)
    {
        // 从第一行开始，不需要分页
        if (startRowIndex <= 0 && maximumRows < 1) return sql;

        return SqlServer.PageSplitByTopNotIn(sql, startRowIndex, maximumRows, keyColumn);
    }
    #endregion
}

/// <summary>SqlCe会话</summary>
class SqlCeSession : FileDbSession
{
    #region 构造函数
    public SqlCeSession(IDatabase db) : base(db) { }
    #endregion

    protected override void CreateDatabase()
    {
        if (String.IsNullOrEmpty(FileName) || File.Exists(FileName)) return;

        //FileSource.ReleaseFile(Assembly.GetExecutingAssembly(), "SqlCe.sdf", FileName, true);
        DAL.WriteLog("创建数据库：{0}", FileName);

        var sce = SqlCeEngine.Create(Database.ConnectionString);
        if (sce != null) sce.CreateDatabase().Dispose();
    }

    /// <summary>执行插入语句并返回新增行的自动编号</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns>新增行的自动编号</returns>
    public override Int64 InsertAndGetIdentity(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        BeginTransaction(IsolationLevel.Serializable);
        try
        {
            Int64 rs = Execute(sql, type, ps);
            if (rs > 0) rs = ExecuteScalar<Int64>("Select @@Identity");
            Commit();
            return rs;
        }
        catch
        {
            Rollback(true);
            throw;
        }
    }

    /// <summary>返回数据源的架构信息</summary>
    /// <param name="conn">连接</param>
    /// <param name="collectionName">指定要返回的架构的名称。</param>
    /// <param name="restrictionValues">为请求的架构指定一组限制值。</param>
    /// <returns></returns>
    public override DataTable GetSchema(DbConnection conn, String collectionName, String[] restrictionValues)
    {
        //sqlce3.5 不支持GetSchema
        if (SqlCe.SqlCeProviderVersion < SQLCEVersion.SQLCE40 && collectionName.EqualIgnoreCase(DbMetaDataCollectionNames.MetaDataCollections))
            return null;

        return base.GetSchema(conn, collectionName, restrictionValues);
    }
}

/// <summary>SqlCe元数据</summary>
class SqlCeMetaData : FileDbMetaData
{
    #region 构架
    protected override List<IDataTable> OnGetTables(String[] names)
    {
        #region 查表、字段信息、索引信息、主键信息
        var session = Database.CreateSession();

        // 表信息
        var dt = session.Query(_AllTableNameSql).Tables[0];

        var data = new NullableDictionary<String, DataTable>(StringComparer.OrdinalIgnoreCase)
        {
            ["Columns"] = session.Query(_AllColumnSql).Tables[0],
            ["Indexes"] = session.Query(_AllIndexSql).Tables[0]
        };

        ////数据类型DBType --〉DotNetType转换
        //if (SqlCe.SqlCeProviderVersion < SQLCEVersion.SQLCE40)
        //    DataTypes = CreateSqlCeDataType(session.Query(_DataTypeSql).Tables[0]);
        #endregion

        if (dt == null || dt.Rows == null || dt.Rows.Count <= 0) return null;

        // 默认列出所有字段
        var rows = dt.Select("TABLE_TYPE='table'");
        if (rows == null || rows.Length <= 0) return null;

        return GetTables(rows, names, data);
    }

    /// <summary>
    /// 快速取得所有表名
    /// </summary>
    /// <returns></returns>
    public override IList<String> GetTableNames()
    {
        var list = new List<String>();

        var dt = GetSchema(_.Tables, null);
        if (dt?.Rows == null || dt.Rows.Count <= 0) return list;

        // 默认列出所有字段
        var rows = dt.Select("TABLE_TYPE='table'");

        foreach (var dr in rows)
        {
            var tn = GetDataRowValue<String>(dr, _.TalbeName);
            if (!tn.IsNullOrEmpty()) list.Add(tn);
        }

        return list;
    }

    /// <summary>获取索引</summary>
    /// <param name="table"></param>
    /// <param name="indexes">索引</param>
    /// <param name="indexColumns">索引列</param>
    /// <returns></returns>
    protected override List<IDataIndex> GetIndexes(IDataTable table, DataTable? indexes, DataTable? indexColumns)
    {
        var list = base.GetIndexes(table, indexes, indexColumns);

        // SqlCe的索引直接以索引字段的方式排布，所以需要重新组合起来
        var dic = new Dictionary<String, IDataIndex>();
        foreach (var item in list)
        {
            if (item.Name.IsNullOrEmpty()) continue;

            if (!dic.TryGetValue(item.Name, out var di))
            {
                dic.Add(item.Name, item);
            }
            else
            {
                var ss = new List<String>(di.Columns);
                if (item.Columns != null && item.Columns.Length > 0 && !ss.Contains(item.Columns[0]))
                {
                    ss.Add(item.Columns[0]);
                    di.Columns = ss.ToArray();
                }
            }
        }
        list.Clear();
        foreach (var item in dic.Values)
        {
            list.Add(item);
        }

        return list;
    }

    public override String CreateTableSQL(IDataTable table)
    {
        var sql = base.CreateTableSQL(table);

        var pks = table.PrimaryKeys;
        if (String.IsNullOrEmpty(sql) || pks == null || pks.Length < 2) return sql;

        // 处理多主键
        var sb = new StringBuilder(sql.Length + 32 + pks.Length * 16);
        sb.Append(sql);
        sb.Append(";\r\n");
        sb.AppendFormat("Alter Table {0} Add Constraint PK_{1} Primary Key ({2})", FormatName(table), table.TableName, pks.Join(",", FormatName));

        //sql += ";" + Environment.NewLine;
        //sql += String.Format("Alter Table {0} Add Constraint PK_{1} Primary Key ({2})", FormatName(table.TableName), table.TableName, sb);
        return sb.ToString();
    }

    protected override String? GetFieldConstraints(IDataColumn field, Boolean onlyDefine)
    {
        // 非定义时（修改字段），主键字段没有约束
        if (!onlyDefine && field.PrimaryKey) return null;

        var str = base.GetFieldConstraints(field, onlyDefine);

        // 非定义时，自增字段没有约束
        if (onlyDefine && field.Identity) str = " IDENTITY(1,1)" + str;

        return str;
    }

    #endregion

    #region 取得字段信息的SQL模版

    private DataTable CreateSqlCeDataType(DataTable src)
    {
        var drs = src.Select();
        foreach (var dr in drs)
        {
            dr["datatype"] = DBTypeToDotNetDataType(dr["typename"].ToString());
        }
        src.AcceptChanges();
        return src;
    }

    private String DBTypeToDotNetDataType(String DBType)
    {
        return DBType switch
        {
            "smallint" => "System.Int16",
            "int" => "System.Int32",
            "bigint" => "System.Int64",
            "nvarchar" or "char" or "nchar" or "ntext" or "text" or "varchar" => "System.String",
            "bit" => "System.Boolean",
            "smalldatetime" or "datetime" => "System.DateTime",
            "float" => "System.Double",
            "decimal" or "money" or "smallmoney" or "numeric" => "System.Decimal",
            "real" => "System.Single",
            "uniqueidentifier" => "System.Guid",
            "tinyint" => "System.Byte",
            "image" or "timestamp" or "binary" or "varbinary" => "System.Byte[]",
            "variant" => "System.Object",
            _ => "",
        };
    }

    private readonly String _AllTableNameSql = "SELECT table_name,TABLE_TYPE FROM information_schema.tables WHERE TABLE_TYPE <> N'SYSTEM TABLE' ";

    private readonly String _AllColumnSql =
            "SELECT     Column_name, is_nullable, data_type, character_maximum_length, numeric_precision, autoinc_increment as AUTOINCREMENT, autoinc_seed, column_hasdefault, column_default, column_flags, numeric_scale, table_name, autoinc_next  " +
            "FROM         information_schema.columns " +
            "WHERE      SUBSTRING(COLUMN_NAME, 1,5) <> '__sys'  " +
            "ORDER BY ordinal_position ASC ";

    private readonly String _AllIndexSql =
            "SELECT     TABLE_NAME, INDEX_NAME, PRIMARY_KEY, [UNIQUE], [CLUSTERED], ORDINAL_POSITION, COLUMN_NAME, COLLATION AS SORT_ORDER " +
            "FROM         Information_Schema.Indexes " +
            "WHERE      SUBSTRING(COLUMN_NAME, 1,5) <> '__sys'   " +
            "ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION";


    //private readonly String _DataTypeSql =
    //        "SELECT     TYPE_NAME as typename, DATA_TYPE as ProviderDbType,TYPE_NAME as datatype, COLUMN_SIZE as ColumnSize, LITERAL_PREFIX as LiteralPrefix, " +
    //        "           LITERAL_SUFFIX as LiteralSuffix, CREATE_PARAMS as CreateParameters, IS_NULLABLE as IsNullable, CASE_SENSITIVE as IsCaseSensitive, " +
    //        "           SEARCHABLE as IsSearchable, UNSIGNED_ATTRIBUTE as IsUnsigned, FIXED_PREC_SCALE, AUTO_UNIQUE_VALUE, LOCAL_TYPE_NAME,  " +
    //        "           MINIMUM_SCALE as MinimumScale, MAXIMUM_SCALE as MaximumScale, GUID , TYPELIB , VERSION , IS_LONG as IsLong, BEST_MATCH as IsBestMatch, IS_FIXEDLENGTH as IsFixedLength  " +
    //        " FROM      INFORMATION_SCHEMA.PROVIDER_TYPES ";
    #endregion

    /// <summary>数据类型映射</summary>
    private static readonly Dictionary<Type, String[]> _DataTypes = new()
    {
        { typeof(Byte[]), new String[] { "varbinary({0})", "timestamp", "binary({0})", "image" } },
        { typeof(Guid), new String[] { "uniqueidentifier" } },
        { typeof(Boolean), new String[] { "bit" } },
        { typeof(Byte), new String[] { "tinyint" } },
        { typeof(Int16), new String[] { "smallint" } },
        { typeof(Int32), new String[] { "int" } },
        { typeof(Int64), new String[] { "bigint" } },
        { typeof(Single), new String[] { "real" } },
        { typeof(Double), new String[] { "float" } },
        { typeof(Decimal), new String[] { "money", "numeric({0}, {1})" } },
        { typeof(DateTime), new String[] { "datetime" } },
        { typeof(String), new String[] { "nvarchar({0})", "ntext", "nchar({0})" } }
    };
}

/// <summary>SqlCe版本</summary>
public enum SQLCEVersion
{
    /// <summary>Sqlce Ver2.0</summary>
    SQLCE20 = 0,

    /// <summary>Sqlce Ver3.0</summary>
    SQLCE30 = 1,

    /// <summary>Sqlce Ver3.5</summary>
    SQLCE35 = 2,

    /// <summary>Sqlce Ver4.0</summary>
    SQLCE40 = 3
}

/// <summary>SqlCe辅助类</summary>
public static class SqlCeHelper
{
    static readonly Dictionary<Int32, SQLCEVersion> versionDictionary = new()
    {
        { 0x73616261, SQLCEVersion.SQLCE20 },
        { 0x002dd714, SQLCEVersion.SQLCE30 },
        { 0x00357b9d, SQLCEVersion.SQLCE35 },
        { 0x003d0900, SQLCEVersion.SQLCE40 }
    };

    /// <summary>检查给定SqlCe文件的版本</summary>
    /// <param name="fileName"></param>
    /// <returns></returns>
    public static SQLCEVersion DetermineVersion(String fileName)
    {
        var versionLONGWORD = 0;

        using (var fs = new FileStream(fileName, FileMode.Open))
        {
            fs.Seek(16, SeekOrigin.Begin);
            using var reader = new BinaryReader(fs);
            versionLONGWORD = reader.ReadInt32();
        }

        if (versionDictionary.ContainsKey(versionLONGWORD))
            return versionDictionary[versionLONGWORD];
        else
            throw new ApplicationException("不能确定该sdf的版本！");
    }

    /// <summary>检测SqlServerCe3.5是否安装</summary>
    /// <returns></returns>
    public static Boolean IsV35Installed()
    {
        try
        {
            Assembly.Load("System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
        }
        catch (FileNotFoundException)
        {
            return false;
        }
        try
        {
            var factory = DbProviderFactories.GetFactory("System.Data.SqlServerCe.3.5");
        }
        catch (ConfigurationException)
        {
            return false;
        }
        catch (ArgumentException)
        {
            return false;
        }
        return true;
    }

    /// <summary>检测SqlServerCe4是否安装</summary>
    /// <returns></returns>
    public static Boolean IsV40Installed()
    {
        try
        {
            Assembly.Load("System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
        }
        catch (FileNotFoundException)
        {
            return false;
        }
        try
        {
            var factory = DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0");
        }
        catch (ConfigurationException)
        {
            return false;
        }
        catch (ArgumentException)
        {
            return false;
        }
        return true;
    }
}

class SqlCeEngine : IDisposable
{
    /// <summary></summary>
    public static Type EngineType { get; set; } = "System.Data.SqlServerCe.SqlCeEngine".GetTypeEx(true);

    /// <summary>引擎</summary>
    public Object Engine { get; set; }

    public static SqlCeEngine Create(String connstr)
    {
        if (EngineType == null) return null;
        if (String.IsNullOrEmpty(connstr)) return null;

        try
        {
            var e = EngineType.CreateInstance(connstr);
            if (e == null) return null;

            var sce = new SqlCeEngine()
            {
                Engine = e
            };
            return sce;
        }
        catch { return null; }
    }

    public void Dispose() => Engine.TryDispose();

    public SqlCeEngine CreateDatabase() { Engine.Invoke("CreateDatabase"); return this; }

    public SqlCeEngine Shrink() { Engine.Invoke("Shrink"); return this; }
}
#endif